package io.renren.modules.evaluate.service.imp;

import io.renren.modules.evaluate.controller.PingyinTest;
import io.renren.modules.evaluate.dao.NormMesDao;
import io.renren.modules.evaluate.dao.UpLoadMapper;
import io.renren.modules.evaluate.entity.Judge;
import io.renren.modules.evaluate.entity.M;
import io.renren.modules.evaluate.entity.NormMesEntity;
import io.renren.modules.evaluate.entity.Q;
import io.renren.modules.evaluate.service.ExcelService;
import io.renren.modules.evaluate.util.ExcelUtil;
import io.renren.modules.sys.controller.SysLoginController;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


@Service
public class ExcelServiceImpl implements ExcelService {


    @Autowired
    UpLoadMapper upLoadMapper;

    @Autowired
    NormMesDao normMesDao;

    @Autowired
    SysLoginController sysLoginController;

    @Override
    public List<Object> dealFromFile(MultipartFile file) {
        /*得到表名*/

        String name = file.getOriginalFilename();
        name = ExcelUtil.getFileNameNoEx(name);
       if (upLoadMapper.selname(name)!=null){

           List<Object> l = new ArrayList<>();
           Q q = new Q();
           q.setCode("提示");
           q.setContent("文件名重复");
           l.add(q);
           return l;
       }
        upLoadMapper.savename(name,sysLoginController.userid);
        System.out.println("名字是"+name);
        if (file.isEmpty()) {
            throw new RuntimeException("上传的文件为空!");
        }
        List<Object> list = dealExcelWithFile(file,name);
    /*    list.forEach(System.out::println);*/

        return list;
    }

    @Override
    public List<Object> dealFromUri(String fileUri) {
        if ("".equals(fileUri)) {
            // 文件路径为空抛出异常
            throw new RuntimeException("文件路径为空!");
        }
        List<Object> list = dealExcelWithUri(fileUri);
        list.forEach(System.out::println);
        return list;
    }


    // 提取excel中的数据
    private List<Object> dealExcelWithUri(String fileUri) {
        List<Object> list = new ArrayList<>();
        Workbook workbook = readExcelWithUri(fileUri);
        if (workbook != null) {
            // 读取第一个sheet（工作簿）
            Sheet sheet = workbook.getSheetAt(0);
            // 获取最大行数
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            // 读取第一行
            Row row = sheet.getRow(0);
            // 获取最大列数
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();

            String[] columns = new String[1024];
            for (int i = 0; i < physicalNumberOfCells; i++) {
                columns[i] = row.getCell(i).getStringCellValue();
            }
            for (int i = 1; i <= physicalNumberOfRows; i++) {
                Map<String, String> map = new LinkedHashMap<String, String>();
                row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < physicalNumberOfCells; j++) {
                        String cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                    list.add(map);
                } else {
                    break;
                }
            }
        }
        return list;
    }


    private List<Object> dealExcelWithFile(MultipartFile file,String name) {
        List<Object> list = new ArrayList<>();
        Workbook workbook = readExcelWithFile(file);
        /*放list2*/
        List<Object> list2 = new ArrayList<>();
        List<NormMesEntity> list3 = new ArrayList<>();

        if (workbook != null) {
            // 读取第一个sheet（工作簿）
            Sheet sheet = workbook.getSheetAt(0);
            // 获取最大行数
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            // 读取第一行
            Row row = sheet.getRow(0);

            System.out.println(row);
            // 获取最大列数
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();

            String[] columns = new String[1024];
            List<String> l = new ArrayList<>();
            for (int i = 0; i < physicalNumberOfCells; i++) {
                NormMesEntity nms = new NormMesEntity();
                M m = new M();
                columns[i] = row.getCell(i).getStringCellValue();
/*                System.out.println(columns[i]);*/
                /*属性名*/
                m.setName(columns[i]);
                m.setPinyin(PingyinTest.getAllPinyin(columns[i]));
                l.add(PingyinTest.getAllPinyin(columns[i]));
                list2.add(m);

                nms.setNormName(PingyinTest.getAllPinyin(columns[i]));
                nms.setMes(columns[i]);
                normMesDao.save(nms);

/*                System.out.println(columns2[i]);*/
            }
            /*动态建表*/
            upLoadMapper.creattbl(l,name);
            /*------------指标库新增----------------*/

            /*----------------------------*/
            for (int i = 1; i <= physicalNumberOfRows; i++) {
                Map<String, String> map = new LinkedHashMap<String, String>();
                Map<String, String> map1 = new LinkedHashMap<String, String>();
                row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < physicalNumberOfCells; j++) {
                        String cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                        map1.put(l.get(j),cellData);
                    }
                    list.add(map1);
                    /*动态读取excel*/
                   upLoadMapper.save(map1,name);

                } else {
                    break;
                }

            }
        }
        System.out.println(list);
        list.add(list2);
        return list;
    }


    /**
     * 读取excel返回不同版本的workbook
     *
     * @param filePath
     * @return
     */
    public Workbook readExcelWithUri(String filePath) {
        Workbook wb = null;
        if (filePath == null || "".equals(filePath)) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        try (InputStream is = new FileInputStream(filePath)) {
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 读取excel返回不同版本的workbook
     * @param file 文件流
     * @return Workbook
     */
    public Workbook readExcelWithFile(MultipartFile file) {
        Workbook wb = null;
        if (file.isEmpty()) {
            return null;
        }
        String extString = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        try (InputStream is = file.getInputStream()) {
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 处理不同单元格的值
     *
     * @param cell
     * @return
     */
    public Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                //数字
                case NUMERIC:
                    cellValue = String.valueOf(cell);
                    break;
                //字符串
                case STRING:
                    cellValue = String.valueOf(cell.getStringCellValue());
                    break;
                //Boolean
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                //公式
                case FORMULA:
                    try {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                    break;
                //空值
                case BLANK:
                    cellValue = "null";
                    break;
                //故障
                case ERROR:
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }

    public Long get(Long userid){
        return userid;
    }



}
